import json
import requests
import pymysql

#船舶定期查验申请
BASE_URL = 'http://cmp.msa.gov.cn/supervision/shipport/shipPortAction.action?act=getDeclareQueryList&shipPortQO.ship_port_type=regularInspect&shipPortQO.ship_imo=&shipPortQO.ship_name_cn=&shipPortQO.voyage_no=&shipPortQO.declare_date_fr=2024-10-01&shipPortQO.declare_date_to=2024-12-31&shipPortQO.expected_arrival_date_fr=&shipPortQO.expected_arrival_date_to=&shipPortQO.org_code=140000&shipPortQO.in_out_mark=A&shipPortQO.declare_source=A&shipPortQO.status_mark=A'
POST_URL = 'http://cmp.msa.gov.cn/supervision/shipportaudit/shipPortAuditAction.action?act=getHandleList&shipPortQO.ship_port_type=regularInspect&shipPortQO.declare_no='
headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'cZvBXv4YGV0%2FQ6M2xXurltNh1249ZhkECZ1OXjjYKgocUjaoQtYPlJlgySIFTKH67VFWfvF7Lbw%2Frrt8dS6ZARFSVcGwCDLAbfYeILa08CdVlWOhKGoOgqwfQ%2B5C2JplaPSPeyjgn594EMrynKmsLQlghjLmtODsD3pcqDOY5VNaMvlIhF8AcbHFnWj88sR6K7ToD%2B8fWT7JUtytMSWOrPX4KPxmpogoDwpD%2FgSUx5qCKkOqxNVCYqoJkGP6vdpN4idWneegXlpDP4dOCImUZy1buStkpkX5ypqkw%2FShddb%2BsIm5SeMghyH%2FLsVTSNpmlSRqjo7C6zb%2BGb2lK0NPRn6DJt14OyBsgleTRm5iU86YXVvCwRC4x8e2gdI%2BZeGL%2B3rpCyBglupxxivRMZTEZz0TRJ8RdhZMofWYWTLpjk7qOK%2FsWYLOZY8JXcm2r%2F6s%2Bj6bO%2FaZTMHrll8DlaQOaZzCMklxxLpDgx5TxNkpdk0%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': '5h8iqyLGtoAMZ1EX4WU4gKDgg7CZWtFouWrVbPqOsvV-X10KP1lj!-1034909004!1740043395782	'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
print(response_lo.text)
strData = json.loads(response_lo.text)


if response_lo.status_code == 200:
    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_safety_ship_port_permission'
    table_auditor = 'xt_safety_ship_port_permission_auditor'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)
                declare_no = record['declare_no']
                print(declare_no)
                #POST_URL = POST_URL + json.dumps(names)
                #print(POST_URL)
                #print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # 获取申请单审核员信息
                response_auditor = session.post(POST_URL + declare_no, headers=headers, cookies=cookies)
                print(response_auditor.text)
                auditorData = json.loads(response_auditor.text)
                if response_auditor.status_code == 200:
                    for auditor in auditorData:
                        # 获取需要插入的字段名称
                        auditor_fields = ', '.join(auditor.keys())
                        # 拼装需要插入字段对应的%号个数
                        auditor_values = ", ".join(['%s'] * len(auditor))
                        insert_auditor = (
                            "INSERT INTO `{table}` ({fields}) "
                            "VALUES ({values})"
                        ).format(
                            table=table_auditor,
                            fields=auditor_fields,
                            values=auditor_values
                        )

                        cursor.execute(insert_auditor, tuple(auditor.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()